package org.qf.dao.impl;


import com.mysql.cj.util.StringUtils;
import com.sun.javafx.iio.common.SmoothMinifier;
import org.qf.dao.SmbmsUserDao;
import org.qf.pojo.SmbmsUser;
import org.qf.utils.BaseDao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SmbmsUserDaoImpl extends BaseDao implements SmbmsUserDao {


    //通过用户编码查询用户信息
    @Override
    public SmbmsUser queryUserByUserCode(String userCode) throws Exception {
        SmbmsUser smbmsUser = new SmbmsUser();
        String sql = "select  * from smbms_user where userCode=?";
        Connection conn = getConnection1();
        Object[] params = {userCode};

        smbmsUser = queryForOne(conn, sql, SmbmsUser.class, userCode);

        return smbmsUser;
    }

    @Override
    public int upDatepw(String oldPassword, String newPassword) throws Exception {
        int count = 0;
        Connection conn = getConnection();
        try {
            String sql = "update smbms_user set userPassword=? where userPassword=?";
            return update(conn, sql, newPassword, oldPassword);
        } catch (Exception e) {
            e.printStackTrace();
            return count;
        } finally {
            close(conn);
        }
    }

    //查询用户信息并支持模糊查询
    @Override
    public List<SmbmsUser> queryUserByUserName(String username, int userRole, int currentPageNo, int pageSize) throws Exception {
        List<SmbmsUser> list = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        List<Object> obj = new ArrayList<Object>();
        sql.append("select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id");
        if (!StringUtils.isNullOrEmpty(username)) {
            sql.append(" and u.userName like ?");
            obj.add("%" + username + "%");
        }
        if (userRole > 0) {
            sql.append(" and u.userRole=?");
            obj.add(userRole);
        }
        //分页
        sql.append(" order by u.creationDate desc limit ?,?");
        obj.add((currentPageNo - 1) * pageSize);
        obj.add(pageSize);
        Object[] params = obj.toArray();
        System.out.println(sql.toString() + "========================");
        System.out.println(params + "=====================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while (resultSet.next()) {
            SmbmsUser smbmsUser = new SmbmsUser();
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setGender(resultSet.getInt("gender"));
            smbmsUser.setBirthday(resultSet.getDate("birthday"));
            smbmsUser.setPhone(resultSet.getString("phone"));
            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
            //把对象放在集合中
            list.add(smbmsUser);
        }

        return list;
    }

    @Override
    public int getSmbmsUserCount(String username, int userRole) throws Exception {
        int count = 0;
        StringBuffer sql = new StringBuffer();
        List<Object> obj = new ArrayList<Object>();
        sql.append("select count(*) from smbms_user u inner join smbms_role r on u.userRole=r.id");
        if (!StringUtils.isNullOrEmpty(username)) {
            sql.append(" and u.userName like ?");
            obj.add("%" + username + "%");
        }
        if (userRole > 0) {
            sql.append(" and u.userRole=?");
            obj.add(userRole);
        }
        Object[] params = obj.toArray();
        System.out.println(sql + "==========================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        return count;
    }

    @Override
    public SmbmsUser queryUserById(Long id) throws Exception {
        SmbmsUser smbmsUser = new SmbmsUser();
        String sql = "select u.*,r.roleName as userRoleName from smbms_user u inner join smbms_role r on u.userRole=r.id and u.id=?";
        Object[] params = {id};
        ResultSet resultSet = executeSQL(sql, params);
        while (resultSet.next()) {
            smbmsUser.setId(resultSet.getLong("id"));
            smbmsUser.setUserCode(resultSet.getString("userCode"));
            smbmsUser.setUserName(resultSet.getString("userName"));
            smbmsUser.setGender(resultSet.getInt("gender"));
            smbmsUser.setBirthday(resultSet.getDate("birthday"));
            smbmsUser.setPhone(resultSet.getString("phone"));
            smbmsUser.setAddress(resultSet.getString("address"));
            smbmsUser.setUserRoleName(resultSet.getString("userRoleName"));
        }
        return smbmsUser;
    }

    @Override
    public int updateUser(SmbmsUser smbmsUser) throws Exception {
        int count = 0;
        String sql = "update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=? where id=?";
        Object[] params = {smbmsUser.getUserName(), smbmsUser.getGender(), smbmsUser.getBirthday(), smbmsUser.getPhone(), smbmsUser.getAddress(), smbmsUser.getUserRole(), smbmsUser.getId()};
        count = executeUpdate(sql, params);
        return count;
    }

    @Override
    public int addUser(SmbmsUser smbmsUser) throws Exception {
        Connection conn = null;
        int count = 0;
        try {
            conn = getConnection();
            String sql = "insert into smbms_user(userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, creationDate)values(?,?,?,?,?,?,?,?,?,?)";
/*
     Object[] params = {smbmsUser.getUserCode(), smbmsUser.getUserName(), smbmsUser.getUserPassword(), smbmsUser.getGender(), smbmsUser.getBirthday(), smbmsUser.getPhone(), smbmsUser.getAddress(), smbmsUser.getUserRole(), smbmsUser.getCreatedBy(), smbmsUser.getCreationDate()};
*/
            return update(conn, sql, smbmsUser.getUserCode(), smbmsUser.getUserName(),
                    smbmsUser.getUserPassword(), smbmsUser.getGender(), smbmsUser.getBirthday(),
                    smbmsUser.getPhone(), smbmsUser.getAddress(), smbmsUser.getUserRole(),
                    smbmsUser.getCreatedBy(), smbmsUser.getCreationDate());
        } catch (Exception e) {
            e.printStackTrace();
            return count;
        } finally {
            close(conn);
        }

    }

    @Override
    public int deleteUser(Long id) throws Exception {
        int count = 0;
        String sql = "delete from smbms_user where id=?";
        Object[] params = {id};
        count = executeUpdate(sql, params);
        return count;
    }
}
